package org.panda.tcmoc.persistence.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.panda.tcmoc.config.Configure;
import org.panda.tcmoc.domain.SpProduct;
import org.panda.tcmoc.persistence.dao.SpProductDao;
import org.panda.tcmoc.persistence.db.DbHelper;
import org.panda.tcmoc.persistence.db.impl.DbHelperImpl;

public class SpProductDaoImpl implements SpProductDao {
	private DbHelper _dbHelper = new DbHelperImpl();
	private Logger logger = Logger.getLogger(SpProductDaoImpl.class);

	public List<SpProduct> getAllMasterRecords() {
		List<SpProduct> list = new ArrayList<SpProduct>();

		Connection conn = _dbHelper.getConn();
		String sql = "select * from StructuredProduct";
		SpProduct sp = new SpProduct();
		try {
			Statement st = conn.createStatement();
			st.execute(sql);
			ResultSet rs = st.getResultSet();

			while (rs.next()) {
				sp = this.fillSpByResultSet(rs);
				list.add(sp);
			}

			rs.close();
			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public SpProduct getMasterByDealTicketNo(String dealTicketNo) {
		Connection conn = _dbHelper.getConn();
		String sql = "select * from StructuredProduct where dealTicketNo=?";
		SpProduct sp = new SpProduct();
		try {
			PreparedStatement st = conn.prepareStatement(sql);
			st.setString(1, dealTicketNo);
			st.execute();
			ResultSet rs = st.getResultSet();
			while (rs.next()) {
				sp = this.fillSpByResultSet(rs);
			}
			rs.close();
			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return sp;
	}

	public SpProduct getTmpByDealTicketNo(String dealTicketNo) {
		Connection conn = _dbHelper.getConn();
		String sql = "select * from StructuredProduct_T where dealTicketNo=?";
		SpProduct sp = new SpProduct();
		try {
			PreparedStatement st = conn.prepareStatement(sql);
			st.setString(1, dealTicketNo);
			st.execute();
			ResultSet rs = st.getResultSet();
			while (rs.next()) {
				sp = this.fillSpByResultSet(rs);
			}
			rs.close();
			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return sp;
	}

	private PreparedStatement fillPreparedStatementByDomain(
			PreparedStatement st, SpProduct sp) throws SQLException {
		logger.info(sp);
		st.setString(1, sp.getDealTicketNo());
		st.setDouble(2, sp.getCnyAmount());
		st.setString(3, sp.getPkSeqNo());
		st.setString(4, sp.getBranchNo());
		st.setString(5, sp.getGtdNo());
		st.setString(6, sp.getProductName());
		st.setString(7, sp.getProductCode());
		st.setString(8, sp.getCcy());
		st.setDouble(9, sp.getNomialPrinAc());
		st.setString(10, Configure.getKey(sp.getAcType()));
		st.setString(11, sp.getCounterPartyName());
		st.setString(12, Configure.getKey(sp.getCounterPartyType()));
		st.setString(13, sp.getCounterPartyCode());
		st.setString(14, Configure.getKey(sp.getOperationType()));
		st.setString(15, Configure.getKey(sp.getAgreementType()));
		st.setDate(16, new java.sql.Date(sp.getBeginDate().getTime()));
		st.setDate(17, new java.sql.Date(sp.getEndDate().getTime()));
		st.setString(18, Configure.getKey(sp.getTxnType()));
		st.setInt(19, sp.getVersion());
		st.setInt(20, sp.getSubVersion());
		st.setString(21, sp.getStatus());
		st.setString(22, sp.getMakerId());
		st.setString(23, sp.getCheckId());
		st.setTimestamp(24, new java.sql.Timestamp(sp.getMakerTime().getTime()));
		st.setTimestamp(25, new java.sql.Timestamp(sp.getCheckerTime()
				.getTime()));

		st.setString(26, sp.getGtdRecon());
		st.setString(27, sp.getConfFiSend());
		st.setString(28, sp.getConfFiSend());
		st.setString(29, sp.getConfCustomer());
		st.setString(30, sp.getPreCcy());
		st.setDouble(31, sp.getPreFee());
		st.setDate(32, new java.sql.Date(sp.getFxValueDate().getTime()));

		return st;

	}

	/**
	 * @throws SQLException
	 */
	private SpProduct fillSpByResultSet(ResultSet rs) throws SQLException {

		SpProduct sp = new SpProduct();
		sp.setDealTicketNo(rs.getString("DealTicketNo"));
		sp.setCnyAmount(rs.getDouble("cnyAmount"));
		sp.setPkSeqNo(rs.getString("PkSeqNo"));
		sp.setBranchNo(rs.getString("BranchNo"));
		sp.setGtdNo(rs.getString("GtdNo"));
		sp.setProductName(rs.getString("ProductName"));
		sp.setProductCode(rs.getString("ProductCode"));
		sp.setCcy(rs.getString("Ccy"));
		sp.setNomialPrinAc(rs.getDouble("NominalPrinAc"));
		sp.setAcType(Configure.ACCOUNT_TYPE_MAP.get(rs.getString("AcType")));
		sp.setCounterPartyName(rs.getString("CounterPartyName"));
		sp.setCounterPartyType(Configure.COUNTER_PARTY_TYPE_MAP.get(rs
				.getString("CounterPartyType")));
		sp.setCounterPartyCode(rs.getString("CounterPartyCode"));
		sp.setOperationType(Configure.OPERATION_TYPE_MAP.get(rs
				.getString("OperationType")));
		sp.setAgreementType(Configure.AGREEMENT_TYPE_MAP.get(rs
				.getString("AgreementType")));
		sp.setBeginDate(rs.getDate("BeginDate"));
		sp.setEndDate(rs.getDate("EndDate"));
		sp.setTxnType(Configure.TXN_TYPE_MAP.get(rs.getString("TxnType")));

		sp.setStatus(rs.getString("Status"));
		sp.setVersion(rs.getInt("Version"));
		sp.setSubVersion(rs.getInt("SubVersion"));
		sp.setMakerId(rs.getString("makerId"));
		sp.setCheckId(rs.getString("checkerId"));
		sp.setMakerTime(rs.getDate("makerTime"));
		sp.setCheckerTime(rs.getDate("checkerTime"));

		sp.setGtdNo(rs.getString("GtdRecon"));
		sp.setConfFiSend(rs.getString("ConfFiSend"));
		sp.setConfFiRecv(rs.getString("ConfFiRecv"));
		sp.setConfCustomer(rs.getString("ConfCustomer"));
		sp.setPreCcy(rs.getString("PreCcy"));
		sp.setPreFee(rs.getDouble("PreFee"));
		sp.setFxValueDate(rs.getDate("FxValueDate"));

		return sp;
	}

	public List<SpProduct> getRecordsByQuery(String sql) {
		List<SpProduct> list = new ArrayList<SpProduct>();

		Connection conn = _dbHelper.getConn();
		SpProduct sp = new SpProduct();
		try {
			Statement st = conn.createStatement();
			st.execute(sql);
			ResultSet rs = st.getResultSet();

			while (rs.next()) {
				sp = this.fillSpByResultSet(rs);
				list.add(sp);
			}

			rs.close();
			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int updateStatue(SpProduct sp, String status) {
		Connection conn = _dbHelper.getConn();
		String updateStatus = "UPDATE StructuredProduct set status=? where dealTicketNo=?";
		int result = -1;
		try {
			PreparedStatement ps = conn.prepareStatement(updateStatus);
			ps.setString(1, status);
			ps.setString(2, sp.getDealTicketNo());
			result = ps.executeUpdate();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int deleteMaster(SpProduct sp) {
		Connection conn = _dbHelper.getConn();
		String updateStatus = "DELETE from StructuredProduct where dealTicketNo=?";
		int result = -1;
		try {
			PreparedStatement ps = conn.prepareStatement(updateStatus);
			ps.setString(1, sp.getDealTicketNo());
			result = ps.executeUpdate();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int deleteTmp(SpProduct sp) {
		logger.info("delete sp:" + sp);
		Connection conn = _dbHelper.getConn();
		String updateStatus = "DELETE from StructuredProduct_T where dealTicketNo=?";
		int result = -1;
		try {
			PreparedStatement ps = conn.prepareStatement(updateStatus);
			ps.setString(1, sp.getDealTicketNo());
			result = ps.executeUpdate();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public SpProduct getOneByDealTicketNoAndStatus(String dealTicketNo,
			String status) {
		Connection conn = _dbHelper.getConn();
		String sql = "select * from StructuredProduct where dealTicketNo=? and status=?";
		SpProduct sp = new SpProduct();
		try {
			PreparedStatement st = conn.prepareStatement(sql);
			st.setString(1, dealTicketNo);
			st.setString(2, status);
			st.execute();
			ResultSet rs = st.getResultSet();
			while (rs.next()) {
				sp = this.fillSpByResultSet(rs);
			}
			rs.close();
			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return sp;
	}

	public int insertMaster(SpProduct sp) {
		Connection conn = _dbHelper.getConn();
		String insertSql = "INSERT INTO StructuredProduct VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		int result = -1;
		try {
			PreparedStatement ps = conn.prepareStatement(insertSql);
			ps = this.fillPreparedStatementByDomain(ps, sp);
			result = ps.executeUpdate();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int insertTmp(SpProduct sp) {
		Connection conn = _dbHelper.getConn();
		logger.info("INSERT INTO TMP:" + sp);
		String insertSql = "INSERT INTO StructuredProduct_T VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		int result = -1;
		try {
			PreparedStatement ps = conn.prepareStatement(insertSql);
			ps = this.fillPreparedStatementByDomain(ps, sp);
			result = ps.executeUpdate();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int insertHistory(SpProduct sp) {
		logger.info("insert into history:" + sp);
		Connection conn = _dbHelper.getConn();
		String insertSql = "INSERT INTO StructuredProduct_H VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		int result = -1;
		try {
			PreparedStatement ps = conn.prepareStatement(insertSql);
			ps = this.fillPreparedStatementByDomain(ps, sp);
			result = ps.executeUpdate();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public int updateTmpStatus(SpProduct sp, String status) {
		Connection conn = _dbHelper.getConn();
		String updateStatus = "UPDATE StructuredProduct_T set status=? where dealTicketNo=?";
		int result = -1;
		try {
			PreparedStatement ps = conn.prepareStatement(updateStatus);
			ps.setString(1, status);
			ps.setString(2, sp.getDealTicketNo());
			result = ps.executeUpdate();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

}
